<?php
/** 
*
* @package phpBB3
* 
* @author David Lewis ( http://startrekguide.com ) - Highway of Life
* 
* @version $Id: functions_newsletter.php 72 2007-06-25 12:48:35Z highwayoflife $
* @copyright (c) 2007 Highway of Life
* @license: undetermined
*
*/

/**
 * install the newsletter
 */
function install_newsletter()
{
	global $db, $phpbb_root_path, $phpEx, $newsletter_version;
	// MySQL versions prior to 4.1.3 can’t handle Charsets, so we find out what we are dealing with here
	switch ($db->sql_layer)
	{
		case 'mysql':
			$db_type = 'mysql_40';
		break;
	
		case 'mysql4':
			if (version_compare($db->mysql_version, '4.1.3', '>='))
			{
				$db_type = 'mysql_41';
			}
			else
			{
				$db_type = 'mysql_40';
			}
		break;
	
		case 'mysqli':
			$db_type = 'mysql_41';
		break;
	}
	
	// create the newsletter table
	$sql = 'CREATE TABLE ' . NEWSLETTER_POST_TABLE . " (
				newsletter_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
				post_id int(11) UNSIGNED NOT NULL default '0',
				post_author_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
				post_order_id tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
				topic_reply_id int(11) UNSIGNED NOT NULL DEFAULT '0',
				news_post_header varchar(255) NOT NULL DEFAULT '',
				news_post_desc varchar(255) NOT NULL DEFAULT '',
				news_post_date int(11) UNSIGNED NOT NULL DEFAULT '0',
				PRIMARY KEY (newsletter_id, post_id)
			)" . (($db_type == 'mysql_41') ? ' CHARACTER SET utf8 COLLATE utf8_bin;' : ';');
	$db->sql_query($sql);
	
	$sql = 'CREATE TABLE ' . NEWSLETTER_TABLE . " (
				newsletter_id mediumint(8) UNSIGNED NOT NULL auto_increment,
				newsletter_title varchar(255) NOT NULL DEFAULT '',
				newsletter_subject varchar(255) NOT NULL DEFAULT '',
				newsletter_desc varchar(255) NOT NULL DEFAULT '',
				newsletter_date int(11) UNSIGNED NOT NULL DEFAULT '0',
				PRIMARY KEY newsletter_id (newsletter_id)
			)" . (($db_type == 'mysql_41') ? ' CHARACTER SET utf8 COLLATE utf8_bin;' : ';');
	$db->sql_query($sql);
	
	$sql_ary = array(
		'newsletter_subject'	=> 'My First Newsletter',
		'newsletter_title'		=> 'First Newsletter',
		'newsletter_desc'		=> 'This is my first Newsletter, I hope you like it',
		'newsletter_date'		=> time(),
	);

	$sql = 'INSERT INTO ' . NEWSLETTER_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);
	$db->sql_query($sql);
	$newsletter_id = $db->sql_nextid();
	
	// insert some initial values...
	// For the actual MOD, we should do a query and insert the last 3 topics
	$sql_ary = array(
		array(
			'newsletter_id'		=> $newsletter_id,
			'post_id'			=> 1,
			'post_author_id'	=> 2,
			'post_order_id'		=> 1,
			'topic_reply_id'	=> 1,
			'news_post_header'	=> 'This is an example header',
			'news_post_desc'	=> 'An example of your installation',
			'news_post_date'	=> time(),
		),
	);
	$db->sql_multi_insert(NEWSLETTER_POST_TABLE, $sql_ary);
	
	// Setup $auth_admin class so we can add permission options
	include($phpbb_root_path . 'includes/acp/auth.' . $phpEx);
	$auth_admin = new auth_admin();
	
	// Add permission for manage cvsdb
	$auth_admin->acl_add_option(array(
		'local'		=> array(),
		'global'	=> array('a_manage_newsletter', 'u_read_newsletter')
	));
	
	set_config('newsletter_version', $newsletter_version, false);
}

/**
 * Update the newsletter
 */
function update_newsletter()
{
	global $phpbb_root_path, $phpEx, $config, $newsletter_version;
	
	// always make sure we end up setting the config value to the latest
	set_config('newsletter_version', $newsletter_version, false);
}

function active_weekly_topics()
{
	global $db, $template, $server_path;
	global $user, $phpEx;
	$db = new dbal();
	
	$sql = 'SELECT *
			FROM ' . TOPICS_TABLE . '
			WHERE topic_last_post_time > ' . time() - 604800 . '
			ORDER BY topic_replies_real DESC';
	$result = $db->sql_query_limit($sql, 10, 0);
	
	while ($row = $db->sql_fetchrow($result))
	{
		$forum_id = ($row['forum_id'] == 0) ? 2 : $row['forum_id'];
		
		$template->assign_block_vars('topicrow', array(
			'U_VIEW_TOPIC'			=> append_sid($server_path . 'viewtopic.' . $phpEx, 'f=' . $forum_id . '&amp;t=' . $row['topic_id']),
			'TOPIC_TITLE'			=> $row['topic_title'],
			'S_POST_ANNOUNCE'		=> '',
			'S_POST_STICKY'			=> '',
			'TOPIC_AUTHOR_FULL'		=> get_username_string(),
			'FIRST_POST_TIME'		=> $user->format_date($row['topic_time']),
			'LAST_POST_AUTHOR_FULL'	=> get_username_string(),
			'LAST_POST_TIME'		=> $user->format_date($row['topic_last_post_time']),
			'U_LAST_POST'			=> '',
		));
	}
}

?>